
from sqlalchemy.orm import *
from sqlalchemy.types import * 
from sqlalchemy import create_engine, func
from typing import Optional
from sqlalchemy import select

import rich
class Base(DeclarativeBase):
    def _asdict(self):
        return {column.name: getattr(self, column.name) for column in self.__table__.columns}

class account(Base):
    __tablename__="account"
    account_id : Mapped[Integer] = mapped_column( Integer, nullable=False,primary_key=True, autoincrement=True, )
    account_name : Mapped[String] = mapped_column( String,  )
    account_desc : Mapped[String] = mapped_column( String,  )
    account_created_time : Mapped[String] = mapped_column( String,  default=func.now() )
    account_ext : Mapped[String] = mapped_column( String,  )
    account_group_id: Mapped[Integer] = mapped_column(Integer,)




def select_all(engine):
    with Session(engine) as session :
        return [rec._asdict() for rec in session.query(account).all()]
    
def select_by_col(engine,cond:dict):
    with Session(engine) as session :
        return [rec._asdict() for rec in session.query(account).filter_by(**cond).all()]


def update_one(engine,cond:dict,item:dict):
    with Session(engine) as session:
        record = session.query(account).filter_by(**cond).first()
        if record:
            # Update the record with the new values
            for key, value in item.items():
                setattr(record, key, value)
            session.commit()
            return True
        rich.print(f"[red] {e}")
        return False 
    
def add_one(engine,item:dict):
    try:
        with Session(engine) as session :
            record = account(**item)
            session.add(record)
            session.commit()
            return True 
    except Exception as e:
        rich.print(f"[red] {e}")
        return False
    
def delete_one(engine,cond:dict):
    try:
        with Session(engine) as session :
            record = session.query(account).filter_by(**cond).first()
            session.delete(record)
            session.commit()
            return True 
    except Exception as e:
        rich.print(f"[red] {e}")
        return False  
def delete_batch(engine,cond:dict):
    try:
        with Session(engine) as session :
            records = session.query(account).filter_by(**cond).all()
            for rec in records:
                session.delete(rec)
            session.commit()
            return True 
    except Exception as e:
        rich.print(f"[red] {e}")
        return False  
